﻿Imports HtmlAgilityPack
Imports System.Data.SQLite

Namespace OfflineLibrary_WPF
	Module Languages

		Public Function GetLanguages() As List(Of KeyValuePair(Of String, String))
			Dim returnList As New List(Of KeyValuePair(Of String, String))

			Using conn As New SQLiteConnection(OfflineDataConnectionString)
				conn.Open()
				Dim sql As String = "SELECT LanguageCode, Language FROM Languages ORDER BY LanguageCode"
				Using cmd As New SQLiteCommand(sql, conn)

					Using reader As SQLiteDataReader = cmd.ExecuteReader
						While reader.Read
							returnList.Add(New KeyValuePair(Of String, String)(reader("LanguageCode"), reader("Language")))
						End While

					End Using
				End Using
			End Using

			Return returnList
		End Function

		Public Sub UpdateLanguages()
			Dim allLanguages As List(Of KeyValuePair(Of String, String)) = DownloadLanguages()

			Using conn As New SQLiteConnection(OfflineDataConnectionString)
				conn.Open()
				Using trans As SQLiteTransaction = conn.BeginTransaction
					For Each language In allLanguages
						Dim sql As String = "INSERT OR REPLACE INTO Languages (LanguageCode, Language) VALUES (@LanguageCode, @Language)"
						Using cmd As New SQLiteCommand(sql, conn)

							cmd.Parameters.AddWithValue("@LanguageCode", language.Key)
							cmd.Parameters.AddWithValue("@Language", language.Value)

							cmd.ExecuteNonQuery()
						End Using
					Next

					trans.Commit()
				End Using
			End Using
		End Sub

		Public Function DownloadLanguages() As List(Of KeyValuePair(Of String, String))
			Dim returnList As New List(Of KeyValuePair(Of String, String))

			Dim pageGetter As New HtmlWeb
			Dim page As HtmlDocument = pageGetter.Load("http://wol.jw.org/en/wol/li/r1/lp-e")
			Dim allLanguages As HtmlNodeCollection = page.DocumentNode.SelectSingleNode("//ul[@class='librarySelection']").SelectNodes("li[@data-lang]")

			For Each languageItem In allLanguages
				Dim langaugeCode As String = languageItem.SelectSingleNode("a").GetAttributeValue("href", "").Split("/")(1)
				Dim language As String = languageItem.SelectSingleNode("descendant::li[@class='library']").InnerText
				returnList.Add(New KeyValuePair(Of String, String)(langaugeCode, language))
			Next

			Return returnList
		End Function
	End Module
End Namespace
